﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SQLiteDB
{


    public class SQLiteBase
    {
        readonly SQLiteCommand _cmd;
      

        public SQLiteBase(SQLiteCommand command)
        {
            _cmd = command;
             
        }

        #region DB Info

        public DataTable GetTableStatus()
        {
            return Select("SELECT * FROM sqlite_master;");
        }

        public DataTable GetTableList()
        {
            DataTable dt = GetTableStatus();
            DataTable dt2 = new DataTable();
            dt2.Columns.Add("Tables");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string t = dt.Rows[i]["name"] + "";
                if (t != "sqlite_sequence")
                    dt2.Rows.Add(t);
            }
            return dt2;
        }

        public DataTable GetColumnStatus(string tableName)
        {
            return Select(string.Format("PRAGMA table_info(`{0}`);", tableName));
        }

        public DataTable ShowDatabase()
        {
            return Select("PRAGMA database_list;");
        }

        #endregion

        #region Query

        public void BeginTransaction()
        {
            _cmd.CommandText = "begin transaction;";
            _cmd.ExecuteNonQuery();
        }

        public void Commit()
        {
            _cmd.CommandText = "commit;";
            _cmd.ExecuteNonQuery();
        }

        public void Rollback()
        {
            _cmd.CommandText = "rollback";
            _cmd.ExecuteNonQuery();
        }

        public DataTable Select(string sql)
        {
            return Select(sql, new List<SQLiteParameter>());
        }

        public DataTable Select(string sql, Dictionary<string, object> dicParameters)
        {
            List<SQLiteParameter> lst = GetParametersList(dicParameters);
            return Select(sql, lst);
        }

        public DataTable Select(string sql, IEnumerable<SQLiteParameter> parameters)
        {
            _cmd.CommandText = sql;
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    _cmd.Parameters.Add(param);
                }
            }
            SQLiteDataAdapter da = new SQLiteDataAdapter(_cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public void Execute(string sql)
        {
            Execute(sql, new List<SQLiteParameter>());
        }

        //public void Execute(string sql, Dictionary<string, object> dicParameters)
        //{
        //    List<SQLiteParameter> lst = GetParametersList(dicParameters);
        //    Execute(sql, lst);
        //}

            //public void Execute(string sql, IEnumerable<SQLiteParameter> parameters )
            //{
            //    _cmd.CommandText = sql;
            //    if (parameters != null)
            //    {
            //        foreach (var param in parameters)
            //        {
            //            _cmd.Parameters.Add(param);
            //        }
            //    }
            //    _cmd.ExecuteNonQuery();
            //}

        public int Execute(string sql, IEnumerable<SQLiteParameter> parameters)
        {
            _cmd.CommandText = sql;
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    _cmd.Parameters.Add(param);
                }
            }
          return  _cmd.ExecuteNonQuery();
        }



        public object ExecuteScalar(string sql)
        {
            _cmd.CommandText = sql;
            return _cmd.ExecuteScalar();
        }

        public object ExecuteScalar(string sql, Dictionary<string, object> dicParameters )
        {
            List<SQLiteParameter> lst = GetParametersList(dicParameters);
            return ExecuteScalar(sql, lst);
        }

        public object ExecuteScalar(string sql, IEnumerable<SQLiteParameter> parameters )
        {
            _cmd.CommandText = sql;
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    _cmd.Parameters.Add(parameter);
                }
            }
            return _cmd.ExecuteScalar();
        }

        //public dataType ExecuteScalar<dataType>(string sql, Dictionary<string, object> dicParameters )
        //{
        //    List<SQLiteParameter> lst = null;
        //    if (dicParameters != null)
        //    {
        //        lst = new List<SQLiteParameter>();
        //        foreach (KeyValuePair<string, object> kv in dicParameters)
        //        {
        //            lst.Add(new SQLiteParameter(kv.Key, kv.Value));
        //        }
        //    }
        //    return ExecuteScalar<dataType>(sql, lst);
        //}

        //public dataType ExecuteScalar<dataType>(string sql, IEnumerable<SQLiteParameter> parameters )
        //{
        //    _cmd.CommandText = sql;
        //    if (parameters != null)
        //    {
        //        foreach (var parameter in parameters)
        //        {
        //            _cmd.Parameters.Add(parameter);
        //        }
        //    }
        //    return (dataType)Convert.ChangeType(_cmd.ExecuteScalar(), typeof(dataType),null);
        //}

        public T ExecuteScalar<T>(string sql)
        {
            _cmd.CommandText = sql;
            
            return (T)Convert.ChangeType(_cmd.ExecuteScalar(), typeof(T), null);
        }

        public List<T> ExecuteList<T>(string sql) where T : new() 
        {
            
            DataTable dt = Select(sql);
            if (dt.Rows.Count > 0)
                return ModelHandler<T>.FillModel(dt);
            else return new List<T>();
        }


        private List<SQLiteParameter> GetParametersList(Dictionary<string, object> dicParameters)
        {
            List<SQLiteParameter> lst = new List<SQLiteParameter>();
            if (dicParameters != null)
            {
                foreach (KeyValuePair<string, object> kv in dicParameters)
                {
                    lst.Add(new SQLiteParameter(kv.Key, kv.Value));
                }
            }
            return lst;
        }

        public string Escape(string data)
        {
            data = data.Replace("'", "''");
            data = data.Replace("\\", "\\\\");
            return data;
        }

        public void Insert(string tableName, Dictionary<string, object> dic)
        {
            StringBuilder sbCol = new System.Text.StringBuilder();
            StringBuilder sbVal = new System.Text.StringBuilder();

            foreach (KeyValuePair<string, object> kv in dic)
            {
                if (sbCol.Length == 0)
                {
                    sbCol.Append("insert into ");
                    sbCol.Append(tableName);
                    sbCol.Append("(");
                }
                else
                {
                    sbCol.Append(",");
                }

                sbCol.Append("`");
                sbCol.Append(kv.Key);
                sbCol.Append("`");

                if (sbVal.Length == 0)
                {
                    sbVal.Append(" values(");
                }
                else
                {
                    sbVal.Append(", ");
                }

                sbVal.Append("@v");
                sbVal.Append(kv.Key);
            }

            sbCol.Append(") ");
            sbVal.Append(");");

            _cmd.CommandText = sbCol.ToString() + sbVal.ToString();

            foreach (KeyValuePair<string, object> kv in dic)
            {
                if (kv.Value.GetType().IsEnum)
                {
                    _cmd.Parameters.AddWithValue("@v" + kv.Key, (int)kv.Value);
                }
                else if (kv.Value.GetType()==typeof(DateTime))
                {
                    _cmd.Parameters.AddWithValue("@v" + kv.Key, ((DateTime)kv.Value).ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else
                {
                    _cmd.Parameters.AddWithValue("@v" + kv.Key, kv.Value);
                }
            
            }

            _cmd.ExecuteNonQuery();
        }

        public void InsertPatch<T>( List<T> tlist) where T : new() 
        {
          string  sqlString = InsertPatchString<T>(tlist);
            _cmd.CommandText = sqlString;
            _cmd.ExecuteNonQuery();
        }

        //插入语句
        private string InsertString<T>(T t)
        {
            string tableName = t.GetType().Name;
            Dictionary<string, object> dic = new Dictionary<string, object>();
            foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
            {
                dic.Add(info.Name, info.GetValue(t, null));
            }

            StringBuilder sbCol = new System.Text.StringBuilder();
            StringBuilder sbVal = new System.Text.StringBuilder();

            foreach (KeyValuePair<string, object> kv in dic)
            {
                if (sbCol.Length == 0)
                {
                    sbCol.Append("insert into ");
                    sbCol.Append(tableName);
                    sbCol.Append("(");
                }
                else
                {
                    sbCol.Append(",");
                }


                sbCol.Append(kv.Key);

                if (sbVal.Length == 0)
                {
                    sbVal.Append(" values(");
                }
                else
                {
                    sbVal.Append(", ");
                }

                sbVal.Append("@");
          
                sbVal.Append(kv.Key);
            
            }

            sbCol.Append(") ");
            sbVal.Append(") ");

            return sbCol.ToString() + sbVal.ToString(); ;
        }


        //插入语句
        private string InsertPatchString<T>(List<T> tlist) where T : new()
        {

            T t = new T();
            string tableName = t.GetType().Name;
            Dictionary<string, object> dic = new Dictionary<string, object>();
            foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
            {
                dic.Add(info.Name, info.GetValue(t, null));
            }

            StringBuilder sbCol = new System.Text.StringBuilder();
            StringBuilder sbVal = new System.Text.StringBuilder();

            foreach (KeyValuePair<string, object> kv in dic)
            {
                if (sbCol.Length == 0)
                {
                    sbCol.Append("insert into ");
                    sbCol.Append(tableName);
                    sbCol.Append("(");
                }
                else
                {
                    sbCol.Append(",");
                }


                sbCol.Append(kv.Key);
               
            }
            sbCol.Append(") ");
            if (tlist.Count > 0)
            {
                sbVal.Append(" VALUES ");

                foreach (T tt in tlist)
                {
                    string strsql = string.Empty;
                    strsql += " (";
                    foreach (System.Reflection.PropertyInfo info in tt.GetType().GetProperties())
                    {
                        object obj = info.GetValue(tt, null);
                        if (obj.GetType() == typeof(int) || obj.GetType() == typeof(long) || obj.GetType() == typeof(decimal) || obj.GetType() == typeof(float))
                        {
                            strsql += obj.ToString();
                        }
                        else if (obj.GetType() == typeof(DateTime))
                        {
                            strsql += "'" +((DateTime) obj).ToString("yyyy-MM-dd HH:mm:ss") + "'";
                        }
                        else if (obj.GetType() == typeof(bool))
                        {
                            strsql += (bool)obj?1:0;
                        }
                        else if (obj.GetType().IsEnum)
                        {
                            strsql += (int)obj;
                        }
                        else
                        {

                            strsql += "'" + obj.ToString() + "'";
                        }

                        strsql += ",";
                    }

                    sbVal.Append(strsql.Trim(',')+"),");
                }
            }
 
            return sbCol.ToString() + sbVal.ToString().Trim(',') ;
        }



        public void Update(string tableName, Dictionary<string, object> dicData, string colCond, object varCond)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic[colCond] = varCond;
            Update(tableName, dicData, dic);
        }

        public void Update(string tableName, Dictionary<string, object> dicData, Dictionary<string, object> dicCond)
        {
            if (dicData.Count == 0)
                throw new Exception("dicData is empty.");

            StringBuilder sbData = new System.Text.StringBuilder();

            Dictionary<string, object> _dicTypeSource = new Dictionary<string, object>();

            foreach (KeyValuePair<string, object> kv1 in dicData)
            {
                _dicTypeSource[kv1.Key] = null;
            }

            foreach (KeyValuePair<string, object> kv2 in dicCond)
            {
                if (!_dicTypeSource.ContainsKey(kv2.Key))
                    _dicTypeSource[kv2.Key] = null;
            }

            sbData.Append("update `");
            sbData.Append(tableName);
            sbData.Append("` set ");

            bool firstRecord = true;

            foreach (KeyValuePair<string, object> kv in dicData)
            {
                if (firstRecord)
                    firstRecord = false;
                else
                    sbData.Append(",");

                sbData.Append("`");
                sbData.Append(kv.Key);
                sbData.Append("` = ");

                sbData.Append("@v");
                sbData.Append(kv.Key);
            }

            sbData.Append(" where ");

            firstRecord = true;

            foreach (KeyValuePair<string, object> kv in dicCond)
            {
                if (firstRecord)
                    firstRecord = false;
                else
                {
                    sbData.Append(" and ");
                }

                sbData.Append("`");
                sbData.Append(kv.Key);
                sbData.Append("` = ");

                sbData.Append("@c");
                sbData.Append(kv.Key);
            }

            sbData.Append(";");

            _cmd.CommandText = sbData.ToString();

            foreach (KeyValuePair<string, object> kv in dicData)
            {

                if (kv.Value.GetType().IsEnum)
                {
                    _cmd.Parameters.AddWithValue("@v" + kv.Key,(int) kv.Value);
                }
                else {
                    _cmd.Parameters.AddWithValue("@v" + kv.Key, kv.Value);
                }
              
            }

            foreach (KeyValuePair<string, object> kv in dicCond)
            {
                if (kv.Value.GetType().IsEnum)
                {
                    _cmd.Parameters.AddWithValue("@c" + kv.Key, (int)kv.Value);
                }
                else
                {
                    _cmd.Parameters.AddWithValue("@c" + kv.Key, kv.Value);
                }
      
            }

            _cmd.ExecuteNonQuery();
        }



        public void Delete(string tableName, Dictionary<string, object> dicCond)
        {
            
            StringBuilder sbData = new System.Text.StringBuilder();
            Dictionary<string, object> _dicTypeSource = new Dictionary<string, object>();
            sbData.Append("Delete  From ");
            sbData.Append(tableName);
             
            bool firstRecord = true;

            sbData.Append(" where ");

            firstRecord = true;

            foreach (KeyValuePair<string, object> kv in dicCond)
            {
                if (firstRecord)
                    firstRecord = false;
                else
                {
                    sbData.Append(" and ");
                }

                sbData.Append(" ");
                sbData.Append(kv.Key);
                sbData.Append(" = ");

                sbData.Append("@c");
                sbData.Append(kv.Key);
            }

            sbData.Append(";");

            _cmd.CommandText = sbData.ToString();
 

            foreach (KeyValuePair<string, object> kv in dicCond)
            {
                _cmd.Parameters.AddWithValue("@c" + kv.Key, kv.Value);
            }

            _cmd.ExecuteNonQuery();
        }


        public long LastInsertRowId()
        {
            return ExecuteScalar<long>("select last_insert_rowid();");
        }

        #endregion

        #region Utilities

        public void CreateTable(SQLiteTable table)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("create table if not exists `");
            sb.Append(table.TableName);
            sb.AppendLine("`(");

            bool firstRecord = true;

            foreach (SQLiteColumn col in table.Columns)
            {
                if (col.ColumnName.Trim().Length == 0)
                {
                    throw new Exception("Column name cannot be blank.");
                }

                if (firstRecord)
                    firstRecord = false;
                else
                    sb.AppendLine(",");

                sb.Append(col.ColumnName);
                sb.Append(" ");

                if (col.AutoIncrement)
                {

                    sb.Append("integer primary key autoincrement");
                    continue;
                }

                switch (col.ColDataType)
                {
                    case ColType.VARCHAR:
                        sb.Append("varchar"); break;
                    case ColType.Int:
                        sb.Append("int"); break;
                    case ColType.Decimal:
                        sb.Append("decimal"); break;
                    case ColType.DateTime:
                        sb.Append("datetime"); break;
                    case ColType.BLOB:
                        sb.Append("blob"); break;
                }

                if (col.PrimaryKey)
                    sb.Append(" primary key");
                else if (col.NotNull)
                    sb.Append(" not null");
                else if (col.DefaultValue.Length > 0)
                {
                    sb.Append(" default ");

                    if (col.DefaultValue.Contains(" ") || col.ColDataType == ColType.VARCHAR || col.ColDataType == ColType.DateTime)
                    {
                        sb.Append("'");
                        sb.Append(col.DefaultValue);
                        sb.Append("'");
                    }
                    else
                    {
                        sb.Append(col.DefaultValue);
                    }
                }
            }

            sb.AppendLine(");");

            _cmd.CommandText = sb.ToString();
            _cmd.ExecuteNonQuery();
        }

        public void RenameTable(string tableFrom, string tableTo)
        {
            _cmd.CommandText = string.Format("alter table `{0}` rename to `{1}`;", tableFrom, tableTo);
            _cmd.ExecuteNonQuery();
        }

        public void CopyAllData(string tableFrom, string tableTo)
        {
            DataTable dt1 = Select(string.Format("select * from `{0}` where 1 = 2;", tableFrom));
            DataTable dt2 = Select(string.Format("select * from `{0}` where 1 = 2;", tableTo));

            Dictionary<string, bool> dic = new Dictionary<string, bool>();

            foreach (DataColumn dc in dt1.Columns)
            {
                if (dt2.Columns.Contains(dc.ColumnName))
                {
                    if (!dic.ContainsKey(dc.ColumnName))
                    {
                        dic[dc.ColumnName] = true;
                    }
                }
            }

            foreach (DataColumn dc in dt2.Columns)
            {
                if (dt1.Columns.Contains(dc.ColumnName))
                {
                    if (!dic.ContainsKey(dc.ColumnName))
                    {
                        dic[dc.ColumnName] = true;
                    }
                }
            }

            StringBuilder sb = new System.Text.StringBuilder();

            foreach (KeyValuePair<string, bool> kv in dic)
            {
                if (sb.Length > 0)
                    sb.Append(",");

                sb.Append("`");
                sb.Append(kv.Key);
                sb.Append("`");
            }

            StringBuilder sb2 = new System.Text.StringBuilder();
            sb2.Append("insert into `");
            sb2.Append(tableTo);
            sb2.Append("`(");
            sb2.Append(sb.ToString());
            sb2.Append(") select ");
            sb2.Append(sb.ToString());
            sb2.Append(" from `");
            sb2.Append(tableFrom);
            sb2.Append("`;");

            _cmd.CommandText = sb2.ToString();
            _cmd.ExecuteNonQuery();
        }

        public void DropTable(string table)
        {
            _cmd.CommandText = string.Format("drop table if exists `{0}`", table);
            _cmd.ExecuteNonQuery();
        }

        public void UpdateTableStructure(string targetTable, SQLiteTable newStructure)
        {
            newStructure.TableName = targetTable + "_temp";

            CreateTable(newStructure);

            CopyAllData(targetTable, newStructure.TableName);

            DropTable(targetTable);

            RenameTable(newStructure.TableName, targetTable);
        }

        public void AttachDatabase(string database, string alias)
        {
            Execute(string.Format("attach '{0}' as {1};", database, alias));
        }

        public void DetachDatabase(string alias)
        {
            Execute(string.Format("detach {0};", alias));
        }


        public  bool ExistsTable(string tableName)
        {
            string sql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tableName + "'";
            if (ExecuteScalar(sql).ToString() == "0")
            {
                return false;
            }
            return true;
        }

        #endregion


        
   

    }
}
